package com.ceba.test.modules.sign.dao;

import javax.servlet.http.HttpServletRequest;
import javax.xml.crypto.Data;

import abc.object.IObject;
import com.ceba.cebautils.DateUtil;
import abc.data.DataRow;
import abc.data.Sql;
import com.ceba.base.web.dao.IBaseDao;
import com.ceba.test.base.enums.QrFileUploadFlagEnum;
import com.ceba.test.modules.filemanage.entity.GFile;
import com.ceba.test.modules.statistic.entity.ReportSign;

import abc.ID;
import abc.data.DataTable;
import abc.data.SqlSessionManager;
import abc.data.store.IStore;
import abc.data.store.StoreManager;
import abc.object.XObject;
import org.json.JSONObject;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

public class SignDao extends IBaseDao {
	
	
	/**
	 * 统计所有已签名的报告
	 * 
	 * @param request
	 * @return
	 */
	public  String sumAllSign(HttpServletRequest request) {
		long count2 = SqlSessionManager.getCount(" cb_report_attach ");
		return "" + count2;
	}
	
	
	/**
	 * 添加一个数字证书签名记录
	 * 
	 * @param parm
	 * @return
	 */
	public Boolean addSignRecord(ReportSign  parm)
			throws Exception {
		
		IStore store = StoreManager.create("cb_report_sign");
		XObject object = new XObject("cb_report_sign");
		object.put("ID", ID.getGuid());
		object.put("FILE_ID", parm.getFileId());
		object.put("CREATE_USER_IP", parm.getCreateUserIP());
		object.put("FILE_NAME", parm.getFileName());
		object.put("CREATE_USER_NAME", parm.getCreateUserName());
		object.put("EDITION_TYPE", parm.getEditionType());
		object.put("FILE_TYPE", parm.getFileType());
		object.put("QR_FILE_UPLOAD_FLAG", parm.getQrFileUploadFlagEnum().getCode());
		object.setCreateTime();
		object.setCreateUser();
		if (store.insert(object) > 0) {
			return true;
		}

		return false; 
	}

	/**
	 * 更新签名报告的二维码上传状态
	 * @param id
	 * @param qrFileUploadFlag
	 * @return
	 * @throws Exception
	 */
	public boolean updateReportSign(String id, QrFileUploadFlagEnum qrFileUploadFlag)  throws Exception {
		return SqlSessionManager.execute("update cb_report_sign set QR_FILE_UPLOAD_FLAG = " + qrFileUploadFlag.getCode() + " where id='" + id + "'");
	}

	/**
	 * @return 获取二维码上传失败的文件
	 */
	public List<ReportSign> getReportSign(QrFileUploadFlagEnum qrFileUploadFlag) throws Exception {
		List<ReportSign> signedReports = null;
		Sql sql = new Sql("SELECT cb_report_sign.*, g_file.`FILE_PATH` FROM cb_report_sign LEFT JOIN g_file ON cb_report_sign.`FILE_ID` = g_file.`ID` WHERE cb_report_sign.`FILE_ID` IS NOT NULL AND g_file.`ID` IS NOT NULL AND QR_FILE_UPLOAD_FLAG=?");
		sql.addParameter(qrFileUploadFlag.getCode());
		DataTable dt= SqlSessionManager.getDataTable(sql);
		if(dt !=null && dt.getRowCount() >0){
			signedReports = new ArrayList<>();
			for(int i = 0 ; i < dt.getRowCount() ; i ++) {
				DataRow dataRow = dt.getRow(i);
				ReportSign signedReport = copyDataRowToReportSign(dataRow);
				signedReports.add(signedReport);
			}
		}
		return signedReports;
	}

	private ReportSign copyDataRowToReportSign(DataRow dataRow) {
		ReportSign signedReport = new ReportSign();
		JSONObject dd = dataRow.toJSONObject();

		signedReport.setId(dd.has("ID")?dataRow.getString("ID"):"");
		signedReport.setFileId(dd.has("FILE_ID")?dataRow.getString("FILE_ID"):"");
		signedReport.setCreateUserIP(dd.has("CREATE_USER_IP")?dataRow.getString("CREATE_USER_IP"):"");
		signedReport.setFileName(dd.has("FILE_NAME")?dataRow.getString("FILE_NAME"):"");
		signedReport.setCreateUserName(dd.has("CREATE_USER_NAME")?dataRow.getString("CREATE_USER_NAME"):"");
		signedReport.setEditionType(dd.has("EDITION_TYPE")?dataRow.getInt("EDITION_TYPE"):4);
		signedReport.setFileType(dd.has("FILE_TYPE")?dataRow.getInt("FILE_TYPE"):1);
		signedReport.setCreateTime(dd.has("CREATE_TIME")?DateUtil.dateTrans(dataRow.getDate("CREATE_TIME").toString(),DateUtil.YYYY_MM_DD_HH_MM_SS):"");
		signedReport.setCreateUser(dd.has("CREATE_USER")?dataRow.getString("CREATE_USER"):"");
		signedReport.setQrFileUploadFlagEnum(QrFileUploadFlagEnum.from(dd.has("QR_FILE_UPLOAD_FLAG")?dataRow.getInt("QR_FILE_UPLOAD_FLAG"):QrFileUploadFlagEnum.DEFAULT_NONE.getCode()));
		signedReport.setFilePath(dd.has("FILE_PATH")?dataRow.getString("FILE_PATH"):"");

		return signedReport;
	}
	
	/**
	 * 获取所有签名报告个数
	 * 
	 * @return
	 */
	public  long getAllSignReports() throws Exception {
		return SqlSessionManager.getCount(" cb_report_sign");
	}
	
	
	
	
	/**
	 * 获取所有当前用户签名报告个数
	 * 
	 * @return
	 */
	public  long getCusUserAllSignReports(String userId,int editionType) throws Exception {
	    long num=0;
		DataTable dt = SqlSessionManager.getDataTable("select * from cb_report_sign where cb_report_sign.CREATE_USER='"+userId+"' and cb_report_sign.EDITION_TYPE="+editionType);
		if(dt!=null && dt.getRowCount()>0){
			num=dt.getRowCount();
		}
		return num;
	}

	/**
	 * 获取今年所有签名报告个数
	 * 
	 * @return
	 */
	public  long getYearSignReports() throws Exception {
		Calendar date = Calendar.getInstance();
		int year = Integer.parseInt(String.valueOf(date.get(Calendar.YEAR)));
		System.out.println(year);

		Calendar calendar = Calendar.getInstance();
		calendar.clear();
		calendar.set(Calendar.YEAR, year);
		SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String currYearFirst = f.format(calendar.getTime());
		System.out.println(currYearFirst);

		calendar.clear();
		calendar.set(Calendar.YEAR, year);
		calendar.roll(Calendar.DAY_OF_YEAR, -1);
		calendar.set(Calendar.HOUR, 23);
		calendar.set(Calendar.MINUTE, 59);
		calendar.set(Calendar.SECOND, 59);
		String currYearLast = f.format(calendar.getTime());

		return SqlSessionManager.getCount(" cb_report_sign", " CREATE_TIME BETWEEN '" + currYearFirst + "' and '" + currYearLast + "'");
	}

	
	/**
	 * 获取所有签名附件个数
	 * 
	 * @return
	 */
	public  long getAllSignOReports() throws Exception {
		return SqlSessionManager.getCount(" cb_report_attach ");
	}
	
	/**
	 * 获取今年所有签名附件个数
	 * 
	 * @return
	 */
	public  long getYearSignOReports() throws Exception {
		return SqlSessionManager.getCount(" cb_report_attach", "  YEAR(SIGN_TIME) = YEAR(NOW())");
	}
	
	
	/**
	 * 获取前一天所有签名报告个数
	 * 
	 * @return
	 */
	public  long getLastDayReportSign() throws Exception {
		return SqlSessionManager.getCount(" cb_report_sign", "  TO_DAYS(NOW())-TO_DAYS(CREATE_TIME) = 1");
	}
	
	
	/**
	 * 获取前一天所有签名附件个数
	 * 
	 * @return
	 */
	public  long getLastDayAttachSign() {
		return SqlSessionManager.getCount(" cb_report_attach ", " TO_DAYS(NOW())-TO_DAYS(SIGN_TIME) = 1");
	}

	public static void  signTest(int count) throws Exception{
		Sql sql = new Sql("select * from cb_report_sign where ID='bbfbe8b4deaa4d6a94d22e94fd36e75c'");
		DataTable dataTable = SqlSessionManager.getDataTable(sql);
		DataRow dataRow = dataTable.getRow(0);
		String id = dataRow.getString("ID");
		String FILE_ID = dataRow.getString("FILE_ID");
		String CREATE_USER_IP = dataRow.getString("CREATE_USER_IP");
		String FILE_NAME = dataRow.getString("FILE_NAME");
		String CREATE_USER_NAME = dataRow.getString("CREATE_USER_NAME");
		int EDITION_TYPE = dataRow.getInt("EDITION_TYPE");
		int FILE_TYPE = dataRow.getInt("FILE_TYPE");
		String CREATE_TIME = dataRow.getString("CREATE_TIME");
		String CREATE_USER = dataRow.getString("CREATE_USER");

		Sql sql1 = new Sql("select * from g_file where CLASS='sign' AND ID='beeb143b93c14218b69452ee7d8021ca'");
		DataTable dataTable1 = SqlSessionManager.getDataTable(sql1);
		DataRow dataRow1 = dataTable1.getRow(0);
		GFile file = new GFile();
		file.setId(dataRow1.getString("ID"));
		file.setFilePath(dataRow1.getString("FILE_PATH"));
		file.setFileName(dataRow1.getString("FILE_NAME"));
		file.setExtName(dataRow1.getString("EXT_NAME"));
		file.setVirtualPath(dataRow1.getString("VIRTUAL_PATH"));
		file.setSize(dataRow1.getLong("SIZE"));
		String contentId = dataRow1.getString("text_content_id");
		Sql sql2 = new Sql("select * from g_file_text_content where id='"+contentId+"'");
		DataTable dataTable2 = SqlSessionManager.getDataTable(sql2);
		DataRow dataRow2 = dataTable2.getRow(0);
		file.setTextContent(dataRow2.getString("TEXT_CONTENT"));
		file.setDocumentType(dataRow1.getInt("DOCUMENT_TYPE"));
		file.setCreateUser(dataRow1.getString("CREATE_USER"));
		file.setCreateTime(dataRow1.getString("CREATE_TIME"));
		file.setClassS(dataRow1.getString("CLASS"));
		file.setStorageAddressId(dataRow1.getString("STORAGE_ADDRESS_ID"));

		for(int i=0;i<count;i++){
			String fileId = ID.getGuid();
			String signId = ID.getGuid();
			insert(signId,fileId,CREATE_USER_IP,FILE_NAME,CREATE_USER_NAME,EDITION_TYPE,FILE_TYPE,CREATE_TIME,CREATE_USER);
			file.setId(fileId);
			addFile(file);
		}
	}

	public static boolean insert(String id,String FILE_ID,String CREATE_USER_IP,String FILE_NAME,String CREATE_USER_NAME,int EDITION_TYPE,int FILE_TYPE,String CREATE_TIME,String CREATE_USER) throws Exception{
		IStore store = StoreManager.create("cb_report_sign");
		XObject object = new XObject("cb_report_sign");
		object.put("ID", id);
		object.put("FILE_ID", FILE_ID);
		object.put("CREATE_USER_IP", CREATE_USER_IP);
		object.put("FILE_NAME", FILE_NAME);
		object.put("CREATE_USER_NAME", CREATE_USER_NAME);
		object.put("EDITION_TYPE", EDITION_TYPE);
		object.put("FILE_TYPE", FILE_TYPE);
		object.put("CREATE_TIME", CREATE_TIME);
		object.put("CREATE_USER", CREATE_USER);
		if (store.insert(object) > 0) {
			return true;
		}
		return false;
	}

	public static boolean addFile(GFile gFile) throws Exception{
		IStore store = StoreManager.create("g_file");
		IObject object = new XObject("g_file");
		object.put("ID",gFile.getId());
		object.put("CONTENT_TYPE",gFile.getContentType());
		object.put("FILE_PATH",gFile.getFilePath());
		object.put("FILE_NAME",gFile.getFileName());
		object.put("EXT_NAME",gFile.getExtName());
		object.put("VIRTUAL_PATH",gFile.getVirtualPath());
		object.put("SIZE",gFile.getSize());
		object.put("DOCUMENT_TYPE",gFile.getDocumentType());
		object.put("CREATE_TIME", DateUtil.getNowTime(null));
		object.put("CREATE_USER",gFile.getCreateUser());
		object.put("SCOPE",gFile.getScope());
		object.put("CLASS",gFile.getClassS());
		object.put("REMARK",gFile.getRemark());
		object.put("VERSION", gFile.getVersion());
		object.put("QRCODE_FILE_ID", gFile.getQrCodeFileId());
		object.put("STORAGE_ADDRESS_ID", gFile.getStorageAddressId());

		//将文件内容存到g_file_text_content表中
		IStore storeContent = StoreManager.create("g_file_text_content");
		IObject objectContent = new XObject("g_file_text_content");
		String contentId = ID.getGuid();
		objectContent.put("ID",contentId);
		objectContent.put("TEXT_CONTENT",gFile.getTextContent());
		objectContent.setCreateTime();
		object.put("text_content_id",contentId);

		if(storeContent.insert(objectContent)>0){
			if(store.insert(object)>0){
				return true;
			}
		}
		return false;
	}

	public int queryFileCount(int count){
		Sql sql = new Sql("select count(ID) as count from g_file where CLASS='sign'");
		DataTable dataTable = SqlSessionManager.getDataTable(sql);
		if(dataTable!=null && dataTable.getRowCount()>0){
			DataRow dataRow = dataTable.getRow(0);
			String str = dataRow.get("count")+"";
			int cou = Integer.getInteger(str);
			if(count>cou){
				int result = count - cou;
				return result;
			}
		}
		return 0;
	}
}
